﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Dapper;
using CCWin;

namespace MDM
{
    public partial class Form1 : Skin_Color
    {
        public Form1()
        {

            InitializeComponent();
        }

        #region Excel上传
        private void UpLoadExcel_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel|*.xls;*.xlsx";
            if (file.ShowDialog() == DialogResult.OK)
            {
                string excelName = file.FileName;
                DataTable table = ExcelHelper.GetImportExcelDataTable(excelName);
                table.Columns[0].ColumnName = "酒店名称";
                table.Columns[1].ColumnName = "酒店编号";
                table.Columns[2].ColumnName = "员工姓名";
                table.Columns[3].ColumnName = "手机号";
                table.Columns[4].ColumnName = "状态";
                table.Columns[5].ColumnName = "邮箱";
                table.Columns[6].ColumnName = "职位级别"; //职位级别分经理和员工
                dataGridView1.DataSource = table;
                dataGridView1.Columns[0].FillWeight = 60;
                dataGridView1.Columns[1].FillWeight = 60;
                dataGridView1.Columns[2].FillWeight = 60;
                dataGridView1.Columns[3].FillWeight = 60;
                dataGridView1.Columns[4].FillWeight = 50;
                dataGridView1.Columns[6].FillWeight = 60;

            }
            文件名.Text = file.FileName;
        }

        /// <summary>
        /// 上传Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            int length = this.dataGridView1.RowCount;
            string errorMessage = "";
            int errornum = 0;
            int successnum = 0;
            for (int i = 0; i < length; i++)
            {
                string a0 = dataGridView1[0, i].Value == null ? "" : dataGridView1[0, i].Value.ToString();//酒店名称
                string a1 = dataGridView1[1, i].Value == null ? "" : dataGridView1[1, i].Value.ToString();//酒店编号
                string a2 = dataGridView1[2, i].Value == null ? "" : dataGridView1[2, i].Value.ToString();//员工姓名
                string a3 = dataGridView1[3, i].Value == null ? "" : dataGridView1[3, i].Value.ToString();//手机号
                string a4 = dataGridView1[4, i].Value == null ? "" : dataGridView1[4, i].Value.ToString();//状态（新增，停用，启用）
                string a5 = dataGridView1[5, i].Value == null ? "" : dataGridView1[5, i].Value.ToString();//邮箱
                string a6 = dataGridView1[6, i].Value == null ? "" : dataGridView1[6, i].Value.ToString();//职位级别 经理-SL_0002   员工 - SL_0001
                string jobgrade = a6.Contains("经理") ? "SL_0002" : "SL_0001";
                if (string.IsNullOrWhiteSpace(a0) || string.IsNullOrWhiteSpace(a1) || string.IsNullOrWhiteSpace(a2)
                   || string.IsNullOrWhiteSpace(a3) || string.IsNullOrWhiteSpace(a4) || string.IsNullOrWhiteSpace(a5))
                {
                    errornum = errornum + 1;
                    errorMessage += errornum + ".数据有空值:" + a0 + a1 + a2 + a3 + a5 + ";\r\n";
                    continue;
                }
                string sql = "select * from sl_userinfo where UserID=" + a3 + " and username='" + a2 + "' ";
                string querymaxeidsql = "select max(Eid) as max from sl_userinfo "; //先找出最大的Eid，之后给他加上1
                using (var conn = SqlHelper.GetConnection())
                {
                    var query = conn.Query(sql);
                    var querymaxeid = conn.Query(querymaxeidsql);
                    if (a4.Contains("新增"))
                    {
                        if (query.Count() == 0)
                        {
                            long eid = querymaxeid.ToList()[0].max + 1;
                            string userid = a3;
                            string salt = Guid.NewGuid().ToString().Replace("-", "").ToUpper(); //生成Guid
                            Random r = new Random();
                            string mingwenPwd = r.Next(10000000, 99999999).ToString();//生成8为数的随机密码          
                            string userPwd = StringExtension.HashSaltPassword(mingwenPwd, salt);//密码        
                            string insertSql = @"insert into sl_userinfo values
                             (@eid,@userid,@userPwd,@username,@salt,@depcode,@hotelcd,@jobgrade,@status,@createdate,@editdate,
                              @certtype,@certno,@birthday,@gender,@tel,@mobile,@email,@edituser) ";
                            int result = conn.Execute(insertSql, new
                            {
                                eid = eid,
                                userid = userid,
                                userPwd = userPwd,
                                username = a2,
                                salt = salt,
                                depcode = "",
                                hotelcd = a1,
                                jobgrade = jobgrade,
                                status = 'N',
                                createdate = DateTime.Now,
                                editdate = DateTime.Now,
                                certtype = "",
                                certno = "",
                                birthday = "",
                                gender = 1,
                                tel = "",
                                mobile = a3,
                                email = a5,
                                edituser = "sys"
                            });
                            if (result > 0)
                            {
                                successnum = successnum + 1;
                            }
                            #region 向临时表中插入记录,然后发邮件。
                            conn.Execute("insert into sl_UserinfoSendEmail values(@phone,@password,@name,@email)", new
                            {
                                phone = a3,
                                password = mingwenPwd,
                                name = a2,
                                email = a5
                            });
                            #endregion
                        }
                        else
                        {
                            errornum = errornum + 1;
                            errorMessage += errornum + ".新增数据有误，数据库中存在该数据:" + a0 + a1 + a2 + a3 + a5 + ";新增失败\r\n";
                        }
                    }
                    if (a4.Contains("停用")) //如果有，停用；没有记录错误
                    {
                        if (query.Count() == 0)
                        {
                            errornum = errornum + 1;
                            errorMessage += errornum + ".数据库中不存在该数据:" + a0 + a1 + a2 + a3 + a5 + "，停用失败;\r\n";
                        }
                        else
                        {
                            string updatesql = "update sl_userinfo set status='C',hotelcd=@hotelcd,jobgrade=@jobgrade, editdate=@editdate where Userid=@mobile and username=@username";
                            int result = conn.Execute(updatesql, new { hotelcd = a1, jobgrade = jobgrade, editdate = DateTime.Now, mobile = a3, username = a2 });
                            if (result > 0)
                            {
                                successnum = successnum + 1;
                            }
                        }
                    }
                    if (a4.Contains("启用")) //如果有，停用；没有记录错误
                    {
                        if (query.Count() == 0)
                        {
                            errornum = errornum + 1;
                            errorMessage += errornum + ".数据库中不存在该数据，" + a0 + a1 + a2 + a3 + a5 + "，启用失败;\r\n";
                        }
                        else
                        {
                            string updatesql = "update sl_userinfo set status='N',hotelcd=@hotelcd,jobgrade=@jobgrade, editdate=@editdate where Userid=@mobile and username=@username";
                            int result = conn.Execute(updatesql, new { hotelcd = a1, jobgrade = jobgrade, editdate = DateTime.Now, mobile = a3, username = a2 });
                            if (result > 0)
                            {
                                successnum = successnum + 1;
                            }
                        }
                    }
                }
                textBox2.Text = errorMessage + "\r\n 成功的条数：" + successnum;
            }
        }

        /// <summary>
        /// 在datagridview显示行号
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            Rectangle rectangle = new Rectangle(e.RowBounds.Location.X, e.RowBounds.Location.Y,
            dataGridView1.RowHeadersWidth - 4, e.RowBounds.Height);
            TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
                   dataGridView1.RowHeadersDefaultCellStyle.Font, rectangle,
                   dataGridView1.RowHeadersDefaultCellStyle.ForeColor,
                   TextFormatFlags.VerticalCenter | TextFormatFlags.Right);

        }

        #endregion

        #region 查询数据
        /// <summary>
        /// 单个查询信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonsearch_Click(object sender, EventArgs e)
        {
            string name = textBoxnmae.Text.Trim();
            string mobile = textBoxmobile.Text.Trim();
            string hotel = textBoxHotel.Text.Trim();
            StringBuilder querysql = new StringBuilder("select   userid ,username as 姓名 ,case when jobgrade='SL_0002' then '经理' when jobgrade='SL_0001' then '员工' end as 职位级别,case when status='N' then '在用' when status='C' then '停用' end as 状态,hotelcd as 酒店编号, mobile as 手机号,email as 邮件 ,createdate as 创建时间 from  sl_userinfo where 1=1 ");//where 1=1后要留空格
            if (string.IsNullOrWhiteSpace(name) && string.IsNullOrWhiteSpace(mobile) && string.IsNullOrWhiteSpace(hotel))
            {
                MessageBox.Show("请输入查询信息");
                return;
            }
            if (!string.IsNullOrWhiteSpace(name))
            {
                querysql.Append("and username like '%" + name + "%' ");
            }
            if (!string.IsNullOrWhiteSpace(mobile))
            {
                querysql.Append("and mobile like '" + mobile + "%' ");
            }
            if (!string.IsNullOrWhiteSpace(hotel))
            {
                querysql.Append("and hotelcd like '%" + hotel + "%' ");
            }

            using (var conn = SqlHelper.GetConnection())
            {
                DataTable table = SqlHelper.ExecuteDataTable(querysql.ToString(), conn);
                dataGridView2.DataSource = table;
                dataGridView2.Columns[0].FillWeight = 80;
                dataGridView2.Columns[1].FillWeight = 80;
                dataGridView2.Columns[2].FillWeight = 70;
                dataGridView2.Columns[3].FillWeight = 50;
                dataGridView2.Columns[4].FillWeight = 70;
            }
        }

        private void dataGridView2_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
            Rectangle rectangle = new Rectangle(e.RowBounds.Location.X, e.RowBounds.Location.Y,
          dataGridView1.RowHeadersWidth - 4, e.RowBounds.Height);
            TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),
                   dataGridView2.RowHeadersDefaultCellStyle.Font, rectangle,
                   dataGridView2.RowHeadersDefaultCellStyle.ForeColor,
                   TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
        }

        /// <summary>
        /// 上传Excel查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ExcelSearch_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel|*.xls;*.xlsx";
            if (file.ShowDialog() == DialogResult.OK)
            {
                string excelName = file.FileName;

                List<string> list = ExcelHelper.queryMobile(excelName);
                StringBuilder querysql = new StringBuilder("");
                foreach (var s in list)
                {
                    querysql.Append("'" + s + "',");
                }

                string sql = "select  userid ,username as 姓名, case when jobgrade='SL_0002' then '经理' when jobgrade='SL_0001' then '员工' end as 职位级别,case when status='N' then '在用' when status='C' then '停用' end as 状态,hotelcd as 酒店编号, mobile as 手机号,email as 邮件 ,createdate as 创建时间 from  sl_userinfo  where mobile in (" + querysql.ToString().Substring(0, querysql.Length - 1) + ") ";

                using (var conn = SqlHelper.GetConnection())
                {
                    DataTable table = SqlHelper.ExecuteDataTable(sql, conn);
                    dataGridView2.DataSource = table;
                    dataGridView2.Columns[0].FillWeight = 80;
                    dataGridView2.Columns[1].FillWeight = 80;
                    dataGridView2.Columns[2].FillWeight = 70;
                    dataGridView2.Columns[3].FillWeight = 50;
                    dataGridView2.Columns[4].FillWeight = 70;
                }
                textBox1.Text = excelName;
            }
        }

        #endregion

        #region 修改和添加

        /// <summary>
        /// 先根据电话查询出来用户信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btupdatesearch_Click(object sender, EventArgs e)
        {
            string mobile = txtupdatemobile.Text.Trim();
            if (string.IsNullOrWhiteSpace( mobile))
            {
                return;
            }
            string sql = "select hotelcd, username,status,  email , jobgrade   from  sl_userinfo where mobile=@mobile";
            using (var conn = SqlHelper.GetConnection())
            {
              var query=  conn.Query(sql,new { mobile=mobile }).ToList();
                if (query.Count()==0)
                {
                    MessageBox.Show("没有这个用户");
                    return;
                }
               txtupdatehotelcd.Text= query[0].hotelcd;
               txtupdateusername.Text = query[0].username;
               cmbupdateStatus.Text= query[0].status.Contains("N") ?"在用":"停用";
               txtupdateemail.Text = query[0].email;
               cmbupdatejobgrade.Text = query[0].jobgrade.Contains("SL_0002") ?"经理":"员工";
               skinGroupBox5.Visible = true;
            }
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btupdate_Click(object sender, EventArgs e)
        {
           string mobile = txtupdatemobile.Text.Trim();
           string hotelcd=txtupdatehotelcd.Text.Trim() ;
           string username= txtupdateusername.Text.Trim()  ;
           string status= cmbupdateStatus.Text.Contains("在用")? "N" : "C";
           string email   = txtupdateemail.Text.Trim();
           string jobgrade= cmbupdatejobgrade.Text.Contains("经理") ? "SL_0002" : "SL_0001";
            using (var conn=SqlHelper.GetConnection())
            {
               string sql = "update sl_userinfo set hotelcd=@hotelcd,username=@username,status=@status,email=@email,jobgrade=@jobgrade,editdate=@editdate where mobile=@mobile";
                int result = conn.Execute(sql, new
                {
                    hotelcd = hotelcd,
                    username = username,
                    status = status,
                    email = email,
                    jobgrade = jobgrade,
                    mobile = mobile,
                    editdate = DateTime.Now
               });
                if (result>0)
                {
                    MessageBox.Show("恭喜您，更新成功");
                    skinGroupBox5.Visible = false;
                }
                else
                {
                    MessageBox.Show("不好意思，更新失败");
                }
            }

        }



        private void btninsert_Click(object sender, EventArgs e)
        {
            string mobile = txtinsertmobile.Text.Trim();
            string hotelcd = txtinserthotelcd.Text.Trim();
            string username = txtinsertusername.Text.Trim();
            string status =   "N";
            string email = txtinsertemail.Text.Trim();
            string jobgrade = cmbinsertjobgrade.Text.Contains("经理") ? "SL_0002" : "SL_0001";
            if (string.IsNullOrWhiteSpace(mobile)|| string.IsNullOrWhiteSpace(hotelcd)||
                string.IsNullOrWhiteSpace(username)|| string.IsNullOrWhiteSpace(email))
            {
                MessageBox.Show("请不要有空值");
                return;
            }
            using (var conn=SqlHelper.GetConnection())
            {
                string sql = "select * from sl_userinfo where UserID=@mobile";
                var query = conn.Query(sql,new { mobile=mobile});
                if (query.Count()>0)
                {
                    MessageBox.Show("已经存在该条数据："+ mobile + "");
                    return;
                }
                string querymaxeidsql = "select max(Eid) as max from sl_userinfo "; //先找出最大的Eid，之后给他加上1
                var querymaxeid = conn.Query(querymaxeidsql);
                long eid = querymaxeid.ToList()[0].max + 1;
                string userid = mobile;
                string salt = Guid.NewGuid().ToString().Replace("-", "").ToUpper(); //生成Guid
                Random r = new Random();
                string mingwenPwd = r.Next(10000000, 99999999).ToString();//生成8为数的随机密码          
                string userPwd = StringExtension.HashSaltPassword(mingwenPwd, salt);//密码        
                string insertSql = @"insert into sl_userinfo values
                             (@eid,@userid,@userPwd,@username,@salt,@depcode,@hotelcd,@jobgrade,@status,@createdate,@editdate,
                              @certtype,@certno,@birthday,@gender,@tel,@mobile,@email,@edituser) ";
                int result = conn.Execute(insertSql, new
                {
                    eid = eid,
                    userid = userid,
                    userPwd = userPwd,
                    username = username,
                    salt = salt,
                    depcode = "",
                    hotelcd = hotelcd,
                    jobgrade = jobgrade,
                    status = status,
                    createdate = DateTime.Now,
                    editdate = DateTime.Now,
                    certtype = "",
                    certno = "",
                    birthday = "",
                    gender = 1,
                    tel = "",
                    mobile = mobile,
                    email = email,
                    edituser = "sys"
                });
                if (result > 0)
                {
                    #region 向临时表中插入记录,然后发邮件。
                    conn.Execute("insert into sl_UserinfoSendEmail values(@phone,@password,@name,@email)", new
                    {
                        phone = mobile,
                        password = mingwenPwd,
                        name = username,
                        email = email
                    });
                    #endregion
                    MessageBox.Show("恭喜你，添加成功");
                }
            
            }
        }

        #endregion
    }
}